import openpyxl
from openpyxl import load_workbook
from tqdm import tqdm
from tools_pmc import (fill_blue, fill_yellow, fill_orange,
                       font_white,
                       lj_tyl, lj_zyl, today_str1, today_str2
                       )

wbwldzfx = load_workbook("//192.168.70.101/19计划管理部/01.计划/5，物控管理/2，呆滞料管理/呆滞数据整理" + today_str1 + "/file/库存明细账" + today_str1 + "-matrix.xlsx")
wskcmx_jz = wbwldzfx["明细矩阵"]
wb = openpyxl.Workbook()
wswldzfx_result = wb.worksheets[0]
wswldzfx_result.title = "分析结果"
x = 0  # 出库为0记数
a = 1  # 当前品号起始行数
b = 0  # #单品号多呆滞

dz_day = 365
for i in tqdm(range(2, wskcmx_jz.max_row, 4)):
    for j in range(6, wskcmx_jz.max_column + 1):
        if wskcmx_jz.cell(i + 1, j).value == 0 and wskcmx_jz.cell(i + 2, j).value != 0:
            x += 1
        elif x > dz_day:
            wswldzfx_result.cell(a, 1).value = wskcmx_jz.cell(i, 1).value
            wswldzfx_result.cell(a, 4).value = wskcmx_jz.cell(i, 4).value
            wswldzfx_result.cell(a, 5).value = str(wskcmx_jz.cell(1, j - x).value) + "——" + str(
                wskcmx_jz.cell(1, j - 1).value)+"（"+str(x)+"天）"
            if wskcmx_jz.cell(i + 1, j).value is not None:
                # 周期截至原因
                wswldzfx_result.cell(a, 6).value = str(wskcmx_jz.cell(1, j).value) + "出库：" + str(
                    wskcmx_jz.cell(i + 1, j).value)
                # 周期截止库存
                wswldzfx_result.cell(a, 7).value = wskcmx_jz.cell(i + 2, j).value - wskcmx_jz.cell(i + 3,
                                                                                                           j).value
                # 周期内入库量
                wswldzfx_result.cell(a, 8).value = wskcmx_jz.cell(i + 2, j - 1).value - wskcmx_jz.cell(i + 2,
                                                                                                               j - x).value
            else:
                wswldzfx_result.cell(a, 6).value = str(wskcmx_jz.cell(1, j - x).value) + "至今无出库记录(已呆滞）"
                wswldzfx_result.cell(a, 6).value = str(wskcmx_jz.cell(1, j-x).value)+"至今无出库记录"
                wswldzfx_result.cell(a, 7).value = wskcmx_jz.cell(i + 2, j-1).value - wskcmx_jz.cell(i + 3,
                                                                                                       j-1).value
                # 周期内入库量
                wswldzfx_result.cell(a, 8).value = wskcmx_jz.cell(i + 2, j - 1).value - wskcmx_jz.cell(i + 2,
                                                                                                           j - x).value

            x = 0
            a += 1
        else:
            x = 0

wb.save("//192.168.70.101/19计划管理部/01.计划/5，物控管理/2，呆滞料管理/呆滞数据整理" + today_str1
        + "/file/分析结果-" + str(dz_day) + "-" + today_str1 + '.xlsx')
